<?php class Mod_fiche_clinique extends CI_Model {
	
	function __construct()
	    {
		parent::__construct();
            }
            function translateNomUrlId($nom_url) {
                $sql="SELECT ID_CLI,NOM_URL FROM clinique"; 
                $Q = $this->db->query($sql);
                $id=0;
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                if($nom_url==$row['NOM_URL']){$id=$row['ID_CLI']; }
			}		 
                }
                $Q->free_result();
                return $id;
            }
            function translateUrl($cl_id) {
                $sql="SELECT ID_CLI,NOM_URL FROM clinique"; 
                $Q = $this->db->query($sql);
                $id=0;
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                if($cl_id==$row['ID_CLI']){$id=$row['ID_CLI']; }
			}		 
                }
                $Q->free_result();
                return $id;
            }
            function getInfoClinicId($id_clinique){
               	$sql = "SELECT * FROM clinique WHERE ID_CLI = $id_clinique";		
		$Q = $this->db->query($sql);
                $data=array();
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data; 
            }
            function getNotreEquipeIdClinic($id_clinique){
                $sql = "SELECT * FROM dentiste WHERE ID_CLI = $id_clinique";		
		$Q = $this->db->query($sql);
                $data=array();
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data; 
                
            }
            /*retourner identifiant langue de navigation*/
            function getIdLangue($lg){
                $sql = "SELECT ID_LANGUE FROM langue WHERE NOM ='$lg'";		
		$Q = $this->db->query($sql);
                $id_lg=0;
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $id_lg=$row['ID_LANGUE'];
			}
		}
		$Q->free_result();
		return $id_lg;
                
            }
            /*retourner la presentation de la clinique suivant la langue de navigation*/
            function getPresentation($lg,$id_cli){
             $sql = "SELECT CONTENU_PRE FROM presenter WHERE ID_LANGUE='$lg' AND ID_CLI='$id_cli'";		
		$Q = $this->db->query($sql);
                $presentation='';
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $presentation=$row['CONTENU_PRE'];
			}
		}
		$Q->free_result();
		return $presentation;   
            }
            /*retourner identifiant dernier commataire du clinique*/
        function getIdLastComment($id_cli){
            $sql = "SELECT * FROM poster WHERE ID_CLI = '$id_cli' ORDER BY DATE_POST DESC LIMIT 1";
		$Q = $this->db->query($sql);		
		$id_com = 0;
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$id_com = $row['ID_COM'];
			}
		}
		$Q->free_result();
		return $id_com;
        }
        /*retourner dernier commentaire de la clinique par l'identifiant*/
        function getLastComment($id_com){
            $sql = "SELECT * FROM commentaire WHERE ID_COM = '$id_com' AND ETAT_COM=1";
		$Q = $this->db->query($sql);		
		$com = '';
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$com = $row['CONTENU_COM'];
			}
		}
		$Q->free_result();
		return $com;
        }
        
        /*retourner la presentation de la clinique suivant la langue de navigation*/
            function getTypesTrait($id_cli){
             $sql = "SELECT * FROM types_traitement WHERE type_id in (SELECT distinct(type_id) "
                     . "FROM cl_trait_tarif WHERE cl_id = $id_cli and valid = 1) and valid = 1";
             //echo "---- getTypesTrait request: $sql </br>";
                $data=array();     
            	$Q = $this->db->query($sql);
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data;   
            }
            
            function getTraitTarifs($id_cli, $type_id){
             $sql = "SELECT * FROM cl_trait_tarif as cl_t, traitement_tarif as t "
                     . "WHERE cl_t.`traitement_id` = t.`traitement_id` "
                     . "AND cl_t.valid =1 AND t.valid = 1 "
                     . "AND cl_t.cl_id = $id_cli AND cl_t.type_id = $type_id ";
             //echo "---- getTraitTarifs request: $sql </br>";
                $data=array();     
            	$Q = $this->db->query($sql);
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data;   
            }
            
            function getGaranties($id_cli){
             $sql = "SELECT * FROM guaranties WHERE valid=1 and id_cl=$id_cli";
                $data=array();     
            	$Q = $this->db->query($sql);
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data;   
            }
            
            function getExperiences($id_equip){
             $sql = "SELECT * FROM experience WHERE valid = 1 and id_equipe = $id_equip";
                $data=array();     
            	$Q = $this->db->query($sql);
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data;   
            }
            
            function getEquipes($id_cl){
             $sql = "SELECT * FROM equipe WHERE ID_CLI= $id_cl ";
                $data=array();     
            	$Q = $this->db->query($sql);
                if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
                                $data[]=$row;
			}
		}
		$Q->free_result();
		return $data;   
            }
            
            /*
             *  $sql = "SELECT * FROM cl_trait_tarif as cl_t, traitement_tarif as t, types_traitement as type "
                     . "WHERE cl_t.`traitement_id` = t.`traitement_id` "
                     . "AND type.type_id = t.type_id "
                     . "AND type.valid =1 AND cl_t.valid =1 AND t.valid = 1 "
                     . "AND cl_t.cl_id = $id_cli";
             * 
             *  SELECT * FROM `experience` WHERE valid = 1 and id_equipe = 1
             */
            
}